Skip Headers

Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) Part Number B14355-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Basic Features

This chapter covers the most basic steps that are taken in creating any Java Database Connectivity (JDBC) application. It also describes the basic features of Java and JDBC supported by the Oracle JDBC drivers.

The following topics are discussed:

- Basic Steps in JDBC

- Sample: Connecting, Querying, and Processing the Results

- Stored Procedure Calls in JDBC Programs

- Processing SQL Exceptions

## Basic Steps in JDBC

When using the Oracle JDBC drivers, you must include certain driver-specific information in your programs. This section describes, in the form of a tutorial, where and how to add the information. The tutorial guides you through creating code to connect to and query a database from the client.

You must write code to perform the following tasks:

1. Importing Packages

2. Opening a Connection to a Database

3. Creating a Statement Object

4. Running a Query and Retrieving a ResultSet Object

5. Processing the ResultSet Object

6. Closing the ResultSet and Statement Objects

7. Making Changes to the Database

8. Committing Changes

9. Closing the Connection

Note: You must supply Oracle driver-specific information for the first three tasks, which allow your program to use the JDBC application programming interface (API) to access a database. For the other tasks, you can use standard JDBC Java code, as you would for any Java application.

### Importing Packages

Regardless of which Oracle JDBC driver you use, include the `import` statements shown in Table 3-1 at the beginning of your program.

Table 3-1 Import Statements for JDBC Driver

Import statement | Provides
--- | ---
`import java.sql.*;` | Standard JDBC packages
`import java.math.*;` | The `BigDecimal` and `BigInteger` classes. You can omit this package if you are not going to use these classes in your application.
`import oracle.jdbc.*;` `import oracle.jdbc.pool.*;` `import oracle.sql.*;` | Oracle extensions to JDBC. This is optional. `OracleDataSource` Oracle type extensions. This is optional.

The Oracle packages listed as optional provide access to the extended functionality provided by the Oracle JDBC drivers, but are not required for the example presented in this section.

Note: It is better to import only the classes your application needs, rather than using the wildcard asterisk (`*`). This document uses asterisk (`*`) for simplicity, but this is not the recommend way of importing classes and interfaces.

### Opening a Connection to a Database

First, you must create an `OracleDataSource` instance. Then, open a connection to the database using `OracleDataSource.getConnection`. The properties of the retrieved connection are derived from the `OracleDataSource` instance. If you set the URL connection property, then all other properties, including `TNSEntryName`, `DatabaseName`, `ServiceName`, `ServerName`, `PortNumber`, `Network Protocol`, and driver type are ignored.

See Also: Table 9-3, "Connection Properties Recognized by Oracle JDBC Drivers"

Specifying a Database URL, User Name, and Password

The following code sets the URL, user name, and password for a data source:

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(url);
    ods.setUser(user);
    ods.setPassword(password);

The following example connects user `scott` with password `tiger` to a database with service `orcl` through port 1521 of the host `myhost`, using the JDBC Thin driver.

    OracleDataSource ods = new OracleDataSource();
    String url = "jdbc:oracle:thin:@//myhost:1521/orcl",
    ods.setURL(url);
    ods.setUser("scott");
    ods.setPassword("tiger");
    Connection conn = ods.getConnection();

Note: The user name and password specified in the arguments override any user name and password specified in the URL.

Specifying a Database URL That Includes User Name and Password

The following example connects user `scott` with password `tiger` to a database host whose TNS entry is `myTNSEntry`, using the JDBC Oracle Call Interface (OCI) driver. In this case, the URL includes the user name and password and is the only input parameter.

    String url = "jdbc:oracle:oci:scott/tiger@myTNSEntry");
    ods.setURL(url);
    Connection conn = ods.getConnection();

If you want to connect using the Thin driver, then you must specify the port number. For example, if you want to connect to the database on `myhost` that has a TCP/IP listener on port 1521 and the service identifier is `orcl`, then provide the following code:

    String URL = "jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl");
    ods.setURL(URL);
    Connection conn = ods.getConnection();

See Also: Chapter 9, "Data Sources and URLs"

### Creating a Statement Object

Once you connect to the database and, in the process, create a `Connection` object, the next step is to create a `Statement` object. The `createStatement` method of the JDBC `Connection` object returns an object of the JDBC `Statement` type. To continue the example from the previous section, where the `Connection` object `conn` was created, here is an example of how to create the `Statement` object:

    Statement stmt = conn.createStatement();

### Running a Query and Retrieving a ResultSet Object

To query the database, use the `executeQuery` method of the `Statement` object. This method takes a SQL statement as input and returns a JDBC `ResultSet` object.

To continue the example, once you create the `Statement` object `stmt`, the next step is to run a query that returns a `ResultSet` object with the contents of the `ENAME` column of a table of employees named `EMP`:

    ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

### Processing the ResultSet Object

Once you run your query, use the `next()` method of the `ResultSet` object to iterate through the results. This method steps through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the appropriate `get` `XXX` methods of the `ResultSet` object, where `XXX` corresponds to a Java data type.

For example, the following code will iterate through the `ResultSet` object, `rset`, from the previous section and will retrieve and print each employee name:

    while (rset.next())
       System.out.println (rset.getString(1));

The `next()` method returns `false` when it reaches the end of the result set. The employee names are materialized as Java `String` values.

### Closing the ResultSet and Statement Objects

You must explicitly close the `ResultSet` and `Statement` objects after you finish using them. This applies to all `ResultSet` and `Statement` objects you create when using the Oracle JDBC drivers. The drivers do not have finalizer methods. The cleanup routines are performed by the `close` method of the `ResultSet` and `Statement` classes. If you do not explicitly close the `ResultSet` and `Statement` objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing both the result set and the statement releases the corresponding cursor in the database. If you close only the result set, then the cursor is not released.

For example, if your `ResultSet` object is `rset` and your `Statement` object is `stmt`, then close the result set and statement with the following lines of code:

    rset.close();
    stmt.close();

When you close a `Statement` object that a given `Connection` object creates, the connection itself remains open.

Note: Typically, you should put `close` statements in a `finally` clause.

### Making Changes to the Database

To write changes to the database, such as for `INSERT` or `UPDATE` operations, you create a `PreparedStatement` object. This enables you to run a statement with varying sets of input parameters. The `prepareStatement` method of the JDBC `Connection` object lets you define a statement that takes variable bind parameters and returns a JDBC `PreparedStatement` object with your statement definition.

Use the `set` `XXX` methods on the `PreparedStatement` object to bind data to the prepared statement to be sent to the database.

See Also: "The setObject and setOracleObject Methods" and "Other setXXX Methods"

The following example shows how to use a prepared statement to run `INSERT` operations that add two rows to the `EMP` table.

    // Prepare to insert new names in the EMP table
        PreparedStatement pstmt =
          conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");

        // Add LESLIE as employee number 1500
        pstmt.setInt (1, 1500);          // The first ? is for EMPNO
        pstmt.setString (2, "LESLIE");   // The second ? is for ENAME
        // Do the insertion
        pstmt.execute ();

        // Add MARSHA as employee number 507
        pstmt.setInt (1, 507);           // The first ? is for EMPNO
        pstmt.setString (2, "MARSHA");   // The second ? is for ENAME
        // Do the insertion
        pstmt.execute ();

        // Close the statement
        pstmt.close();

### Com mitting Changes

By default, data manipulation language (DML) operations are committed automatically as soon as they are run. This is known as the auto-commit mode. However, you can disable auto-commit mode with the following method call on the `Connection` object:

    conn.setAutoCommit(false);

See Also: "Disabling Auto-Commit Mode".

If you disable the auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on the `Connection` object:

    conn.commit();

or:

    conn.rollback();

A `COMMIT` or `ROLLBACK` operation affects all DML statements run since the last `COMMIT` or `ROLLBACK`.

Note: If the auto-commit mode is disabled and you close the connection without explicitly committing or rolling back your last changes, then an implicit `COMMIT` operation is run. Any data definition language (DDL) operation always causes an implicit `COMMIT`. If the auto-commit mode is disabled, then this implicit `COMMIT` will commit any pending DML operations that had not yet been explicitly committed or rolled back.

### Closing the Connection

You must close the connection to the database after you have performed all the required operation and no longer require the connection. You can close the connection by using the `close` method of the `Connection` object, as follows:

    conn.close();

Note: Typically, you should put `close` statements in a `finally` clause.

## Sample: Connecting, Querying, and Processing the Results

The steps in the preceding sections are illustrated in the following example, which uses the Oracle JDBC Thin driver to create a data source, connects to the database, creates a `Statement` object, runs a query, and processes the result set.

Note that the code for creating the `Statement` object, running the query, returning and processing the `ResultSet` object, and closing the statement and connection uses the standard JDBC API.

    import java.sql.*;
    import oracle.jdbc.pool.OracleDataSource;

    class JdbcTest {
       public static void main (String args []) throws SQLException {
          // Create DataSource and connect to the local database
          OracleDataSource ods = new OracleDataSource();
          ods.setURL("jdbc:oracle:thin:@//myhost:1521/orcl");
          ods.setUser("scott");
          ods.setPassword("tiger");
          Connection conn = ods.getConnection();

          // Query the employee names
          Statement stmt = conn.createStatement ();
          ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");
          // Print the name out
          while (rset.next ())
             System.out.println (rset.getString (1));

          //close the result set, statement, and the connection
          rset.close();
          stmt.close();
          conn.close();
       }
    }

If you want to adapt the code for the OCI driver, then replace the call to the `OracleDataSource.setURL` method with the following:

    ods.setURL("jdbc:oracle:oci:@MyHostString");

where, `MyHostString` is an entry in the `TNSNAMES.ORA` file.

## Stored Procedure Calls in JDBC Programs

This section describes how the Oracle JDBC drivers support the following kinds of stored procedures:

- PL/SQL Stored Procedures

- Java Stored Procedures

### PL/SQL Stored Procedures

Oracle JDBC drivers support the processing of PL/SQL stored procedures and anonymous blocks. They support Oracle PL/SQL block syntax and most of SQL92 escape syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

    // SQL92 syntax
    CallableStatement cs1 = conn.prepareCall
                           ( "{call proc (?,?)}" ) ; // stored proc
    CallableStatement cs2 = conn.prepareCall
                           ( "{? = call func (?,?)}" ) ; // stored func
    // Oracle PL/SQL block syntax
    CallableStatement cs3 = conn.prepareCall
                           ( "begin proc (?,?); end;" ) ; // stored proc
    CallableStatement cs4 = conn.prepareCall
                           ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using the Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:

    create or replace function foo (val1 char)
    return char as
    begin
       return val1 || 'suffix';
    end;

The function invocation in your JDBC program should look like:

    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:oci:@<hoststring>");
    ods.setUser("scott");
    ods.setPassword("tiger");
    Connection conn = ods.getConnection();

    CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
    cs.registerOutParameter(1,Types.CHAR);
    cs.setString(2, "aa");
    cs.executeUpdate();
    String result = cs.getString(1);

### Java Stored Procedures

You can use JDBC to call Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly published. That is, you have written call specifications to publish them to the Oracle data dictionary. Applications can call Java stored procedures using the Native Java Interface for direct invocation of `static` Java methods.

## Processing SQL Exceptions

To handle error conditions, the Oracle JDBC drivers throws SQL exceptions, producing instances of the `java.sql.SQLException` class or its subclass. Errors can originate either in the JDBC driver or in the database itself. Resulting messages describe the error and identify the method that threw the error. Additional run-time information can also be appended.

Basic exception-handling can include retrieving the error message, retrieving the error code, retrieving the SQL state, and printing the stack trace. The `SQLException` class includes functionality to retrieve all of this information, where available.

See Also: Appendix C, "JDBC Error Messages" Oracle Database Error Messages

Retrieving Error Information

You can retrieve basic error information with the following methods of the `SQLException` class:

- `getMessage()`

  For errors originating in the JDBC driver, this method returns the error message with no prefix. For errors originating in the database, the method returns the error message prefixed with the corresponding `ORA` number.

- `getErrorCode()`

  For errors originating in either the JDBC driver or the database, this method returns the five-digit `ORA` number.

- `getSQLState()`

  For errors originating in the JDBC driver, this method returns no useful information. For errors originating in the database, this method returns a five-digit code indicating the SQL state. Your code should be prepared to handle `null`.

The following example prints output from a `getMessage()` call:

    catch(SQLException e)
    {
       System.out.println("exception: " + e.getMessage());
    }

This would print the output, such as the following, for an error originating in the JDBC driver:

    exception: Invalid column type

Note: Error message text is available in alternative languages and character sets supported by Oracle.

Printing the Stack Trace

The `SQLException` class provides the `printStackTrace()` method for printing a stack trace. This method prints the stack trace of the throwable object to the standard error stream. You can also specify a `java.io.PrintStream` object or `java.io.PrintWriter` object for output.

The following code fragment illustrates how you can catch SQL exceptions and print the stack trace.

    try { <some code> }
    catch(SQLException e) { e.printStackTrace (); }

To illustrate how the JDBC drivers handle errors, assume the following code uses an incorrect column index:

    // Iterate through the result and print the employee names
    // of the code

    try {
      while (rset.next ())
          System.out.println (rset.getString (5));  // incorrect column index
    }
    catch(SQLException e) { e.printStackTrace (); }

Assuming the column index is incorrect, running the program would produce the following error text:

    java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OracleResultSetImpl.getDate(OracleResultSetImpl.java:1556)
    at Employee.main(Employee.java:41)